﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.IO;

namespace Talentnet.Core.Utilities
{
    public class CSVServices
    {
        public enum DelimiterType
        {
            Tab,
            Semicolon,
            Comma
        }

        public enum EncodingType
        { 
            Unicode,
            ANSI
        }

        public string FolderPath { get; set; }
        public string FileName { get; set; }
        public DelimiterType Delimiter { get; set; }
        public EncodingType Encoding { get; set; }

        public DataTable LoadCSV(int numberOfRows)
        {
            WriteSchema();
            DataSet ds = new DataSet();
            try
            {
                // Creates and opens an ODBC connection
                string connectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + FolderPath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
                string sqlSelect;
                OdbcConnection connection;
                connection = new OdbcConnection(connectionString.Trim());
                connection.Open();

                //Creates the select command text
                if (numberOfRows == -1)
                {
                    sqlSelect = "select * from [" + FileName + "]";
                }
                else
                {
                    sqlSelect = "select top " + numberOfRows + " * from [" + FileName + "]";
                }

                //Creates the data adapter
                OdbcDataAdapter dataAdapter = new OdbcDataAdapter(sqlSelect, connection);

                //Fills dataset with the records from CSV file
                dataAdapter.Fill(ds, "csv");

                //closes the connection
                connection.Close();
            }
            catch (Exception ex) //Error
            {
                return new DataTable();
            }
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            return new DataTable();
        }

        private void WriteSchema()
        {
            try
            {
                FileStream fsOutput = new FileStream(FolderPath + "schema.ini", FileMode.Create, FileAccess.Write);
                StreamWriter srOutput = new StreamWriter(fsOutput);
                string s1, s2, s3, s4, s5;
                s1 = "[" + FileName + "]";
                s2 = "ColNameHeader=True";
                s3 = "Format=TabDelimited";
                s4 = "MaxScanRows=25";
                s5 = "CharacterSet=Unicode";

                srOutput.WriteLine(s1.ToString() + "\r\n" + s2.ToString() + "\r\n" + s3.ToString() + "\r\n" + s4.ToString() + "\r\n" + s5.ToString());
                srOutput.Close();
                fsOutput.Close();
            }
            catch (Exception ex)
            {
                
            }
        }
    }

    
}
